package com.reesechou.util;

import java.lang.reflect.Field;
import java.lang.reflect.Method;


/**
 * 生成数据库增删改查的sql语句(MYSQL)
 * 
 * @author liubao
 * 
 */
public class DBUtils {
	/**
	 * 获取数据库字段方法
	 * 
	 * @author liubao
	 * @return
	 */
	public static String coulums(Class<?> model, String coulum) throws Exception {
		String coulums="";
		if(coulum!=null&&!coulum.equals("")){
		}else{
			Field[] field=field(model);
			for (int i = 0; i < field.length; i++) {
				// 获取属性的名字
				String type = field[i].getGenericType().toString();
				String name=field[i].getName();
				if(type.startsWith("class com.o2o.bean")){
								
				}else{
					// 调用getter方法获取属性值
					coulums=coulums+name+",";
				}
			}
		}
		return coulums.substring(0, coulums.length()-1);
	}

	/**
	 * 转换实体类名称为大写
	 * 
	 * @author liubao
	 * @return
	 */
	private static String objectUpperCase(Class<?> obj) throws Exception {
		String name=obj.toString().replace(".", ",");
		String[] names=name.split(",");
		return (names[names.length-1]).toUpperCase();
	}

	/**
	 * 获取实体类所有的属性
	 * 
	 * @author liubao
	 * @param model
	 * @return Field数组
	 * @throws Exception
	 */
	private static Field[] field(Class<?> model) throws Exception {
		return model.getDeclaredFields();
	}
	/**
	 * 统计个数SQL语句
	 * @param model
	 * @param sql
	 * @return COUNT SQL语句
	 * @throws Exception
	 */
	public static String count(Class<?> model,String sql) throws Exception{
		String sql1 ="SELECT COUNT(1) FROM O2O_"+objectUpperCase(model);
		String wherestr =" WHERE ";
		if(sql==null||sql.equals("")){
			wherestr="";
		}else{
			wherestr=wherestr+sql;
		}
		return sql1+wherestr;
	}
	/**
	 * 获取查询方法的SQL语句
	 * @param model
	 * @param id
	 * @param coulum
	 * @param sql
	 * @return	SELECT SQL语句
	 * @throws Exception
	 */
	private static String query(Class<?> model, Integer id, String coulum,
			String sql) throws Exception {
		Field[] field = field(model);
		String selectstr = "SELECT " + coulums(model, coulum) + " FROM O2O_"
				+ objectUpperCase(model);
		String wherestr = " WHERE ";
		for (int j = 0; j < field.length; j++) {
			// 获取属性的名字
			String name = field[j].getName();
			if (id != null) {
				if (name.equals(objectUpperCase(model) + "_ID")) {
					wherestr = wherestr + name + "=" + id;
					break;
				}
			} else {
				if (sql != null) {
					wherestr = wherestr + sql;
					break;
				} else {
					wherestr = "";
					break;
				}
			}
		}
		return selectstr + wherestr;
	}

	/**
	 * 根据对象编号查询对象的SQL语句
	 * @param model
	 * @param id
	 * @return SELECT SQL语句
	 * @throws Exception
	 */
	public static String find(Class<?> model, Integer id) throws Exception {
		return query(model, id, null, null);
	}

	/**
	 * 根据对象编号查询对象的SQL语句(过滤字段)
	 * @param model
	 * @param id
	 * @param coulum
	 * @return SELECT SQL语句
	 * @throws Exception
	 */
	public static String find(Class<?> model, Integer id, String coulum)
			throws Exception {
		return query(model, id, coulum, null);
	}

	/**
	 * 根据对象条件查询对象的SQL语句
	 * @param model
	 * @param sql
	 * @return SELECT SQL语句
	 * @throws Exception
	 */
	public static String findSQL(Class<?> model, String sql) throws Exception {
		return query(model, null, null, sql);
	}

	/**
	 * 根据对象条件查询对象的SQL语句(过滤字段)
	 * @param model
	 * @param coulum
	 * @param sql
	 * @return SELECT SQL语句
	 * @throws Exception
	 */
	public static String findSQL(Class<?> model, String coulum, String sql)
			throws Exception {
		return query(model, null, coulum, sql);
	}

	/**
	 * 获取查询方法的SQL语句(集合)
	 * @param model
	 * @param coulum
	 * @param sql
	 * @param orderbycoulum
	 * @param orderbytype
	 * @param pageNum
	 * @param pageSize
	 * @return SELECT SQL语句
	 * @throws Exception
	 */
	private static String queryList(Class<?> model, String coulum, String sql,
			String orderbycoulum,String orderbytype,Integer pageNum, Integer pageSize) throws Exception {
		String selectstr = "SELECT " + coulums(model, coulum) + " FROM O2O_"
				+ objectUpperCase(model);
		String wherestr = "";
		String orderby = "";
		String limit = "";
		if (sql != null) {
			wherestr = " WHERE " + sql;
		}
		if(orderbycoulum!=null&&!orderbycoulum.equals("")){
			if(orderbytype!=null&&!orderbytype.equals("")){
				orderby=" ORDER BY "+orderbycoulum+" "+orderbytype;
			}else{
				orderby=" ORDER BY "+orderbycoulum+" ASC";
			}
		}
		if(pageNum==null){
			pageNum=1;
		}
		if(pageNum!=null&&pageSize!=null){
			if(pageNum-1<=0){
				pageNum=1;
			}
			limit=" LIMIT "+(pageNum-1)*pageSize+","+pageSize;
		}
		return selectstr + wherestr + orderby + limit;
	}

	/**
	 * 查询集合的SQL语句
	 * 不进行分页,排序和条件查询
	 * @param model
	 * @return SELECT SQL语句
	 * @throws Exception
	 */
	public static String findList(Class<?> model) throws Exception {
		return queryList(model,null,null,null,null,null,null);
	}
	
	/**
	 * 查询集合的SQL语句
	 * 不进行分页,条件查询
	 * @param model
	 * @param orderbycoulum
	 * @param orderbytype
	 * @return SELECT SQL语句
	 * @throws Exception
	 */
	public static String findList(Class<?> model,String orderbycoulum,String orderbytype) throws Exception{
		return queryList(model,null,null,orderbycoulum,orderbytype,null,null);
	}
	
	/**
	 * 查询集合的SQL语句
	 * 不进行排序,条件查询
	 * @param model
	 * @param pageNum
	 * @param pageSize
	 * @return SELECT SQL语句
	 * @throws Exception
	 */
	public static String findList(Class<?> model,Integer pageNum,Integer pageSize) throws Exception{
		return queryList(model,null,null,null,null,pageNum,pageSize);
	}
	
	/**
	 * 查询集合的SQL语句
	 * 不进行条件查询
	 * @param model
	 * @param orderbycoulum
	 * @param orderbytype
	 * @param pageNum
	 * @param pageSize
	 * @return SELECT SQL语句
	 * @throws Exception
	 */
	public static String findList(Class<?> model,String orderbycoulum,String orderbytype,Integer pageNum,Integer pageSize) throws Exception{
		return queryList(model,null,null,orderbycoulum,orderbytype,pageNum,pageSize);
	}
	
	/**
	 * 根据主键查询集合的SQL语句(字段过滤)
	 * 不进行分页,排序和条件查询
	 * @param model
	 * @param coulum
	 * @return SELECT SQL语句
	 * @throws Exception
	 */
	public static String findList(Class<?> model, String coulum) throws Exception {
		return queryList(model, coulum, null, null, null, null, null);
	}
	
	/**
	 * 查询集合的SQL语句(字段过滤)
	 * 不进行分页,条件查询
	 * @param model
	 * @param coulum
	 * @param orderbycoulum
	 * @param orderbytype
	 * @return SELECT SQL语句
	 * @throws Exception
	 */
	public static String findList(Class<?> model,String coulum,String orderbycoulum,String orderbytype) throws Exception{
		return queryList(model,coulum,null,orderbycoulum,orderbytype,null,null);
	}
	
	/**
	 * 查询集合的SQL语句(字段过滤)
	 * 不进行排序,条件查询
	 * @param model
	 * @param coulum
	 * @param pageNum
	 * @param pageSize
	 * @return SELECT SQL语句
	 * @throws Exception
	 */
	public static String findList(Class<?> model,String coulum,Integer pageNum,Integer pageSize) throws Exception{
		return queryList(model,coulum,null,null,null,pageNum,pageSize);
	}
	
	/**
	 * 查询集合的SQL语句(字段过滤)
	 * 不进行条件查询
	 * @param model
	 * @param coulum
	 * @param orderbycoulum
	 * @param orderbytype
	 * @param pageNum
	 * @param pageSize
	 * @return SELECT SQL语句
	 * @throws Exception
	 */
	public static String findList(Class<?> model,String coulum,String orderbycoulum,String orderbytype,Integer pageNum,Integer pageSize) throws Exception{
		return queryList(model,coulum,null,orderbycoulum,orderbytype,pageNum,pageSize);
	}
	
	/**
	 * 根据自定义条件查询集合的SQL语句
	 * 不进行分页,排序
	 * @param model
	 * @param sql
	 * @return SELECT SQL语句
	 * @throws Exception
	 */
	public static String findListSQL(Class<?> model, String sql) throws Exception {
		return queryList(model, null, sql, null, null, null, null);
	}
	
	/**
	 * 根据自定义条件查询集合的SQL语句
	 * 不进行分页
	 * @param model
	 * @param sql
	 * @param orderbycoulum
	 * @param orderbytype
	 * @return SELECT SQL语句
	 * @throws Exception
	 */
	public static String findListSQL(Class<?> model,String sql,String orderbycoulum,String orderbytype) throws Exception{
		return queryList(model,null,sql,orderbycoulum,orderbytype,null,null);
	}
	
	/**
	 * 根据自定义条件查询集合的SQL语句
	 * 不进行排序
	 * @param model
	 * @param sql
	 * @param pageNum
	 * @param pageSize
	 * @return SELECT SQL语句
	 * @throws Exception
	 */
	public static String findListSQL(Class<?> model,String sql,Integer pageNum,Integer pageSize) throws Exception{
		return queryList(model,null,sql,null,null,pageNum,pageSize);
	}
	
	/**
	 * 查询集合的SQL语句
	 * @param model
	 * @param sql
	 * @param orderbycoulum
	 * @param orderbytype
	 * @param pageNum
	 * @param pageSize
	 * @return SELECT SQL语句
	 * @throws Exception
	 */
	public static String findListSQL(Class<?> model,String sql,String orderbycoulum,String orderbytype,Integer pageNum,Integer pageSize) throws Exception{
		return queryList(model,null,sql,orderbycoulum,orderbytype,pageNum,pageSize);
	}
	
	/**
	 * 根据自定义条件查询集合的SQL语句(字段过滤)
	 * 不进行分页,排序
	 * @param model
	 * @param coulum
	 * @param sql
	 * @return SELECT SQL语句
	 * @throws Exception
	 */
	public static String findListSQL(Class<?> model, String coulum, String sql)
			throws Exception {
		return queryList(model, coulum, sql, null, null, null, null);
	}

	/**
	 * 根据自定义条件查询集合的SQL语句(字段过滤)
	 * 不进行分页
	 * @param model
	 * @param coulum
	 * @param sql
	 * @param orderbycoulum
	 * @param orderbytype
	 * @return SELECT SQL语句
	 * @throws Exception
	 */
	public static String findListSQL(Class<?> model,String coulum,String sql,String orderbycoulum,String orderbytype) throws Exception{
		return queryList(model,coulum,sql,orderbycoulum,orderbytype,null,null);
	}

	/**
	 * 根据自定义条件查询集合的SQL语句(字段过滤)
	 * 不进行排序
	 * @param model
	 * @param coulum
	 * @param sql
	 * @param pageNum
	 * @param pageSize
	 * @return SELECT SQL语句
	 * @throws Exception
	 */
	public static String findListSQL(Class<?> model,String coulum,String sql,Integer pageNum,Integer pageSize) throws Exception{
		return queryList(model,coulum,sql,null,null,pageNum,pageSize);
	}

	/**
	 * 查询集合的SQL语句(字段过滤)
	 * @param model
	 * @param coulum
	 * @param sql
	 * @param orderbycoulum
	 * @param orderbytype
	 * @param pageNum
	 * @param pageSize
	 * @return SELECT SQL语句
	 * @throws Exception
	 */
	public static String findListSQL(Class<?> model,String coulum,String sql,String orderbycoulum,String orderbytype,Integer pageNum,Integer pageSize) throws Exception{
		return queryList(model,coulum,sql,orderbycoulum,orderbytype,pageNum,pageSize);
	}
	/**
	 * 获取修改方法时的数据库SQL
	 * 
	 * @param model
	 * @author liubao
	 * @return update sql语句
	 * @throws Exception
	 */
	public static String update(Object model) throws Exception {
		Field[] field = field(model.getClass());
		String updatestr = "UPDATE O2O_" + objectUpperCase(model.getClass()) + " SET ";
		String wherestr = " WHERE ";
		// 遍历所有属性
		for (int j = 0; j < field.length; j++) {
			// 获取属性的名字
			String name = field[j].getName();
			// 获取属性的类型
			String type = field[j].getGenericType().toString();
			if (name.equals(objectUpperCase(model.getClass()) + "_ID")) {
				Method m = model.getClass().getMethod("get" + name);
				// 调用getter方法获取属性值
				Integer value = (Integer) m.invoke(model);
				if (value != null) {
					wherestr = wherestr + name + "=" + value;
				}
			}
			if (type.equals("class java.lang.String")) {
				Method m = model.getClass().getMethod("get" + name);
				// 调用getter方法获取属性值
				String value = (String) m.invoke(model);
				if (value != null) {
					updatestr = updatestr + name + "='" + value + "',";
				}
			}
			if (type.equals("class java.lang.Double")) {
				Method m = model.getClass().getMethod("get" + name);
				// 调用getter方法获取属性值
				String value = (String) m.invoke(model);
				if (value != null) {
					updatestr = updatestr + name + "=" + value + ",";
				}
			}
			if (type.equals("class java.lang.Integer")) {
				Method m = model.getClass().getMethod("get" + name);
				Integer value = (Integer) m.invoke(model);
				if (value != null) {
					updatestr = updatestr + name + "=" + value + ",";
				}
			}
		}
		updatestr = updatestr.substring(0, updatestr.length() - 1);
		return updatestr + wherestr;
	}

	/**
	 * 获取添加方法时的数据库SQL
	 * 
	 * @param model
	 * @author liubao
	 * @return insert sql语句
	 * @throws Exception
	 */
	public static String insert(Object model) throws Exception {
		Field[] field = field(model.getClass());
		String insertstr = "INSERT INTO O2O_" + objectUpperCase(model.getClass()) + "(";
		String values = " VALUES(";
		// 遍历所有属性
		for (int j = 0; j < field.length; j++) {
			// 获取属性的名字
			String name = field[j].getName();
			// 获取属性的类型
			String type = field[j].getGenericType().toString();
			if (type.equals("class java.lang.Double")) {
				Method m = model.getClass().getMethod("get" + name);
				// 调用getter方法获取属性值
				Double value = (Double) m.invoke(model);
				if (value != null) {
					insertstr = insertstr + name + ",";
					values = values + value + ",";
				}
			}
			if (type.equals("class java.lang.String")) {
				Method m = model.getClass().getMethod("get" + name);
				// 调用getter方法获取属性值
				String value = (String) m.invoke(model);
				if (value != null) {
					insertstr = insertstr + name + ",";
					values = values + "'" + value + "',";
				}
			}
			if (type.equals("class java.lang.Integer")) {
				Method m = model.getClass().getMethod("get" + name);
				Integer value = (Integer) m.invoke(model);
				if (value != null) {
					insertstr = insertstr + name + ",";
					values = values + value + ",";
				}
			}
		}
		insertstr = insertstr.substring(0, insertstr.length() - 1) + ")";
		values = values.substring(0, values.length() - 1) + ")";
		return insertstr + values;
	}

	/**
	 * 获取删除方法时的数据库SQL
	 * 
	 * @param model
	 * @author liubao
	 * @return delete sql语句
	 * @throws Exception
	 */
	public static String delete(Class<?> model) throws Exception {
		Field[] field = field(model);
		String deletestr = "DELETE FROM O2O_" + objectUpperCase(model)
				+ " WHERE ";
		for (int j = 0; j < field.length; j++) {
			// 获取属性的名字
			String name = field[j].getName();
			if (name.equals(objectUpperCase(model) + "_ID")) {
				Method m = model.getMethod("get" + name);
				// 调用getter方法获取属性值
				Integer value = (Integer) m.invoke(model);
				if (value != null) {
					deletestr = deletestr + name + "=" + value;
					break;
				}
			}
		}
		return deletestr;
	}
	/**
	 * 按照编号删除SQL语句
	 * @param model
	 * @param id
	 * @return DELETE 语句
	 * @throws Exception
	 */
	public static String delete(Class<?> model,Integer id) throws Exception{
		Field[] field = field(model);
		String deletestr="";
		String wherestr="";
		if(id!=null){
			deletestr = "DELETE FROM O2O_" + objectUpperCase(model);
			wherestr=" WHERE ";
			for (int j = 0; j < field.length; j++) {
				// 获取属性的名字
				String name = field[j].getName();
				if (name.equals(objectUpperCase(model) + "_ID")) {
					wherestr = wherestr + name + "=" + id;
						break;
				}
			}
		}
		return deletestr+wherestr;
	}
	/**
	 * 按照条件删除SQL语句
	 * @param model
	 * @param sql
	 * @return DELETE 语句
	 * @throws Exception
	 */
	public static String delete(Class<?> model,String sql) throws Exception{
		String deletestr="";
		String wherestr="";
		if(sql!=null&&!sql.equals("")){
			deletestr = "DELETE FROM O2O_" + objectUpperCase(model);
			wherestr=" WHERE "+sql;
		}
		return deletestr+wherestr;
	}
}
